package grsu.epam.airline.DAOImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import grsu.epam.airline.DAO.IEmployeeDAO;
import grsu.epam.airline.DAO.IFlightCrewDAO;
import grsu.epam.airline.model.Employee;
import grsu.epam.airline.model.FlightCrew;

public class FlightCrewDAOImpl implements IFlightCrewDAO {
	private static final String GET_ALL_FLIGHTCREW_QUERY = "select flightcrew_id, flightcrew_name from flightcrew";
	private static final String UPDATE_EMPLOYEE_FLIGHTCREW = "update employees set flightcrew_id=? where employee_id=?";
	private static final String ADD_FLIGHTCREW = "insert into flightcrew (flightcrew_name) values (?)";
	private static final String GET_FLIGHTCREW_BY_ID_QUERY = "select flightcrew_id,flightcrew_name from flightcrew where flightcrew_id=?";
	private static final String DELETE_FLIGHTCREW_EMPLOYEES = "update employees set flightcrew_id=0 where flightcrew_id=?";
	private static final String DELETE_FLIGHTCREW = "DELETE FROM flightcrew WHERE flightcrew_id=?";
	private Connection conn;

	public FlightCrewDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public void addFlightCrew(FlightCrew flightCrew) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(ADD_FLIGHTCREW);
			stmt.setString(1, flightCrew.getFlightCrewName());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public void updateFlightCrew(int flightCrewId, int employeeId)
			throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(UPDATE_EMPLOYEE_FLIGHTCREW);
			stmt.setInt(1, flightCrewId);
			stmt.setInt(2, employeeId);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public FlightCrew getFlightCrewById(int flightCrewId) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		FlightCrew flightCrew = null;
		try {
			stmt = conn.prepareStatement(GET_FLIGHTCREW_BY_ID_QUERY);
			stmt.setInt(1, flightCrewId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				IEmployeeDAO employeeDao = new EmployeeDAOImpl(conn);
				List<Employee> employees = employeeDao
						.getEmployeesByFlightCrew(rs.getInt("flightcrew_id"));
				flightCrew = new FlightCrew(rs.getInt("flightcrew_id"),
						rs.getString("flightcrew_name"), employees);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return flightCrew;
	}

	@Override
	public void deleteFlightCrew(int flightCrewId) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(DELETE_FLIGHTCREW_EMPLOYEES);
			stmt.setInt(1, flightCrewId);
			stmt.executeUpdate();
			stmt = conn.prepareStatement(DELETE_FLIGHTCREW);
			stmt.setInt(1, flightCrewId);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public List<FlightCrew> getAllFlightCrew() throws SQLException {
		List<FlightCrew> flightCrews = new ArrayList<FlightCrew>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(GET_ALL_FLIGHTCREW_QUERY);
			rs = stmt.executeQuery();
			while (rs.next()) {
				IEmployeeDAO employeeDao = new EmployeeDAOImpl(conn);
				List<Employee> employees = employeeDao
						.getEmployeesByFlightCrew(rs.getInt("flightcrew_id"));
				flightCrews.add(new FlightCrew(rs.getInt("flightcrew_id"), rs
						.getString("flightcrew_name"), employees));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return flightCrews;
	}

	@Override
	public Connection getConnection() {
		return this.conn;
	}

}
